package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.Faculty;
import cn.edu.sdjzu.xg.kcsj.service.FacultyService;
import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class FacultyDao {
    private static FacultyDao facultyDao = new FacultyDao();
    private FacultyDao() {}

    public static FacultyDao getInstance() {
        return facultyDao;
    }

    //按no查一条
    public Faculty findByNo(String no,Connection connection) throws SQLException {
        Faculty faculty = null;
        //创建sql语句
        String find_sql = "select * from faculty where no = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setString(1,no);
        //根据no查找
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()) {
            int id=resultSet.getInt("id");
            String description= resultSet.getString("description");
            Faculty parent = FacultyService.getInstance().find( resultSet.getInt("parent_id"));
            Boolean haveChild=resultSet.getBoolean("haveChild");
            faculty=new Faculty(id,description,no,parent,haveChild);
        }
        JdbcHelper.close(resultSet,pstmt);
        return faculty;
    }

    //获得所有学院
    public Collection<Faculty> findAllSchool(Connection connection)throws SQLException{
        //创建集合类对象，用来保存所有记录学院下的所有专业对象
        Collection<Faculty> faculties = new ArrayList<>();
        //创建sql语句
        String find_sql = "select * from faculty";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()) {
            if (resultSet.getInt("parent_id") == 0){
                int id=resultSet.getInt("id");
                String description= resultSet.getString("description");
                String no = resultSet.getString("no");
                Faculty parent = FacultyService.getInstance().find( resultSet.getInt("parent_id"));
                Boolean haveChild=resultSet.getBoolean("haveChild");
                faculties.add(new Faculty(id,description,no,parent,haveChild));
            }
        }
        JdbcHelper.close(resultSet,pstmt);
        return faculties;
    }


    //根据学院id获取下属所有专业
    public Collection<Faculty> findAllDeptBySchoolId(Connection connection,int id)throws SQLException{
        //创建集合类对象，用来保存所有记录学院下的所有专业对象
        Collection<Faculty> faculties = new ArrayList<>();
        //判断是不是学院
        if (findHaveChildById(id, connection)){
            //创建sql语句
            String find_sql = "select * from faculty where parent_id = ? ";
            //在该连接上创建预编译语句对象
            PreparedStatement pstmt = connection.prepareStatement(find_sql);
            //为预编译参数赋值
            pstmt.setInt(1,id);
            //根据id查找
            ResultSet resultSet = pstmt.executeQuery();
            while(resultSet.next()) {
                int idOfDept = resultSet.getInt("id");
                String description= resultSet.getString("description");
                String no = resultSet.getString("no");
                Faculty parent = FacultyService.getInstance().find( resultSet.getInt("parent_id"));
                Boolean haveChild=resultSet.getBoolean("haveChild");
                faculties.add(new Faculty(idOfDept,description,no,parent,haveChild));
            }
            JdbcHelper.close(resultSet,pstmt);
        }
        return faculties;
    }


    //判断是否为学院
    public boolean findHaveChildById(int id , Connection connection) throws SQLException {
        boolean dept = false;
        //创建sql语句
        String find_sql = "select haveChild from faculty where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()) {
            dept=resultSet.getBoolean("haveChild");
        }
        if (dept){
            return true;
        }else{
            return false;
        }
    }


    // 分页+条件
    public Collection<Faculty> findAll(List conditionList, Pagination pagination, Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的faculty对象
        Collection<Faculty> faculties = new TreeSet<Faculty>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection,"faculty");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM faculty ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            int id=resultSet.getInt("id");
            String description= resultSet.getString("description");
            String no = resultSet.getString("no");
            Faculty parent = FacultyService.getInstance().find( resultSet.getInt("parent_id"));
            Boolean haveChild=resultSet.getBoolean("haveChild");
            faculties.add(new Faculty(id,description,no,parent,haveChild));
        }
        JdbcHelper.close(resultSet,pstmt);
        return faculties;
    }


    // 按id查一条
    public Faculty find(int id,Connection connection) throws SQLException {
        Faculty faculty = null;
        //创建sql语句
        String find_sql = "select * from faculty where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()) {
            String description= resultSet.getString("description");
            String no = resultSet.getString("no");
            Faculty parent = FacultyService.getInstance().find( resultSet.getInt("parent_id"));
            Boolean haveChild=resultSet.getBoolean("haveChild");
            faculty=new Faculty(id,description,no,parent,haveChild);
        }
        JdbcHelper.close(resultSet,pstmt);
        return faculty;
    }

    // 更新
    public boolean update(Faculty faculty,Connection connection) throws SQLException {
        //创建sql语句
        String updateSchool_sql = "update faculty set description=?,no=?,parent_id=?,haveChild=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(5,faculty.getId());
        pstmt.setString(1,faculty.getDescription());
        pstmt.setString(2,faculty.getNo());
        pstmt.setInt(3,faculty.getParent().getId());
        pstmt.setBoolean(4,faculty.isHaveChild());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }

    // 添加
    public boolean add(Faculty faculty,Connection connection) throws SQLException {
        //创建sql语句
        String addSchool_sql =
                "insert into faculty (description,no,parent_id,haveChild) values " +
                        "(?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addSchool_sql);
        //为预编译参数赋值
        pstmt.setString(1,faculty.getDescription());
        pstmt.setString(2,faculty.getNo());
        if (faculty.getParent()!=null){
            pstmt.setInt(3,faculty.getParent().getId());
        }else {
            pstmt.setString(3,null);
        }
        pstmt.setBoolean(4,faculty.isHaveChild());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        return affectedRowNum>0;
        //根据结果，具体判断所要返回的值
    }

    // 删除
    public boolean delete(int id,Connection connection) throws SQLException{
        //创建sql语句
        String deleteSchool_sql = "DELETE FROM faculty WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if (pstmt.executeLargeUpdate() == 1){
            return true;
        }else {
            return false;
        }
    }


    public Collection<Faculty> findAllWithoutElse(Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的faculty对象
        Collection<Faculty> faculties = new TreeSet<Faculty>();
        //创建查询的主语句
        String sql_select = "SELECT * FROM faculty";
        PreparedStatement pstmt = connection.prepareStatement(sql_select);
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            int id=resultSet.getInt("id");
            String description= resultSet.getString("description");
            String no = resultSet.getString("no");
            Faculty parent = FacultyService.getInstance().find( resultSet.getInt("parent_id"));
            Boolean haveChild=resultSet.getBoolean("haveChild");
            faculties.add(new Faculty(id,description,no,parent,haveChild));
        }
        JdbcHelper.close(resultSet,pstmt);
        return faculties;
    }

}
